/*******************************************************************************
*Project :          COVID-19 Research Project 

*Purpose : 		    Create Graphs 

*Source : 			Womply Data from AWS 

*******************************************************************************/

clear all 
set more off 
capture log close 

* generate global date
global date=subinstr(c(current_date), " ", "_", .)
set obs 1 
gen date="${date}"
split date, parse("_")
cap replace date=date3+"_"+date2+"_"+date4 if date1==""
cap replace date=date2+"_"+date1+"_"+date3 if date1!=""
global date=date[1]
display as error "Today's date = ${date}"
 

*===============================================================================
* Womply Data 
*===============================================================================

* load womply daily revenue data 
  use "data/clean/womply/womply_daily_revenue.dta" , clear 
  
* get category variables 
  gen clean_cat="Other" 
  replace clean_cat= "Restaurant" if inlist(category,"bars and lounges","food and beverage shops","quick serve food and beverage businesses","restaurants")
  tab category clean_cat, m
  drop category 
  
* aggregate sales to clean_cat 
  bys clean_cat county_fips date : egen rev_ = total(revenue) 
  bys clean_cat county_fips date : keep if _n == 1 
  drop revenue state_name county_name merchants pre_covid_merchant_size ///
	intra_* share_firms_no_rev
  
* reshape data 
  reshape wide rev_  , i(date county_fips) j(clean_cat) string
  
* replace missing values , generate per capita
  foreach var of varlist rev_* { 
	replace `var' = 0 if missing(`var')
	}
	
* get overall value 
  egen rev_All = rowtotal(rev_*)
  
* merge in population weights 
  merge m:1 county_fips using "data/clean/usda_ers/county_information.dta" , keepusing(pop_estimate_2018) ///
	    keep(3) nogen 

* collapse to average daily 
  collapse rev_*  , by(date) 
  
* calculate % change from a year ago , same weekday  
  g dow = dow(date)
  g month = month(date) 
  g year = yofd(date) 
  tab month if year == 2020
  drop if month>3 & year == 2019 
  
 foreach var of varlist rev_* { 
	  g gr_`var' = . 
	  forv day = 0/6{ 
		summ  `var' if year == 2019 & dow == `day'
		local base = `r(mean)' 
		replace gr_`var' = (`var'/`base')*100 - 100 if dow == `day' 
		} 
} 
	
* trim data 
  keep if year == 2020
  keep date gr_rev_* 
  
  tempfile womply 
  save `womply' , replace 
 
 
*===============================================================================
* Second Measure Data 
*===============================================================================

* load womply daily revenue data 
  use "data/clean/second_measure/firm_channel_daily_sales.dta" , clear 
  

* get category variables 
  gen clean_cat= "Other" 
  replace clean_cat= "Online" if channel == "online" 
  replace clean_cat = "Retail" if channel =="retail" 
  tabulate clean_cat , m 
  
* aggregate sales to clean_cat 
  bys company clean_cat date : egen sales_ = total(total_sales) 
  bys company clean_cat date : keep if _n == 1 
  keep company date clean_cat sales_ 
  
* reshape data 
  reshape wide sales_  , i(date company) j(clean_cat) string
  
* replace missing values , generate per capita
  foreach var of varlist sales_* { 
	replace `var' = 0 if missing(`var')
	}

* get overall 
  egen sales_All = rowtotal(sales_*)

* collapse to average daily 
  collapse sales_*  , by(date) 
  
* calculate % change from a year ago , same weekday  
  g dow = dow(date)
  g month = month(date) 
  g year = yofd(date) 
  tab month if year == 2020
  drop if month>=3 & year == 2019 
  
 foreach var of varlist sales_* { 
	  g gr_`var' = . 
	  forv day = 0/6{ 
		summ  `var' if year == 2019 & dow == `day'
		local base = `r(mean)' 
		replace gr_`var' = (`var'/`base')*100 - 100 if dow == `day' 
		} 
} 
	
* trim data 
  keep if year == 2020
  keep date gr_sales_* 
  
* trim to get a national 14-day lag 
  sort date
  /*
  g day_id = _n
  gsort -day_id 
  drop if _n <= 9 
  drop day_id 
  */ 
  
  tempfile second_measure 
  save `second_measure' , replace 

*===============================================================================
* Unacast Data 
*===============================================================================

* load data 
  u "data/clean/unacast/social_distance_county_04_29.dta" , clear
  
* collapse to daily 
  collapse daily_distance_diff daily_visitation_diff , by(date) 
  
  tempfile unacast 
  save `unacast' 
  
  
*===============================================================================
* Append and Create Aggregate Graph 
*===============================================================================

use `unacast' , clear 
  
merge 1:1 date using `womply' 
tab _merge 
drop _merge 

merge 1:1 date using `second_measure' 
tab _merge
drop _merge 

* trim data to April 17 
drop if date>td(17apr2020) 


* get a moving 7-day average 
  tsset date 
   foreach var of varlist daily_distance_diff daily_visitation_diff ///
	gr_rev_Restaurant gr_rev_Other ///
	   gr_rev_All gr_sales_Online gr_sales_Retail gr_sales_All gr_sales_Other { 
	   
	   tssmooth ma `var'_ma = `var' , window(7 1) 
	   drop `var' 
	   rename `var'_ma `var' 
	   } 

	   
tsset date 
format date %tdMonyyyy


label var gr_rev_Other "Womply Other" 
label var gr_rev_Restaurant "Womply Restaurants" 
label var gr_rev_All "Womply Overall" 
label var gr_sales_Online "Second Measure Online" 
label var gr_sales_Retail "Second Measure Brick & Mortar" 
label var gr_sales_Other "Second Measure Sales other than Online / Brick & Mortar" 
label var gr_sales_All "Second Measure Overall" 

save "figures/aggregate/aggregate.dta" , replace


twoway (tsline daily_distance_diff daily_visitation_diff gr_rev_Restaurant gr_rev_Other ///
	   gr_rev_All gr_sales_Online gr_sales_Retail gr_sales_All ,  ///
	   lcolor(blue purple  red orange gold green teal dkgreen))  ///
	   (tsline daily_distance_diff daily_visitation_diff gr_rev_Restaurant gr_rev_Other ///
	   gr_rev_All gr_sales_Online gr_sales_Retail gr_sales_All , yaxis(2) ///
	   lcolor(none none none none none none  none none)) , ///
	   graphregion(color(white)) ///
	   tline(17mar2020 , lcolor(black)) ///
	   legend(order (1 "Distance Travelled, Unacast" ///
				    2 "Visits, Unacast" ///
					3 "Restaurant Sales, Womply" ///
					4 "Other Sales, Womply" ///
					5 "Overall Sales, Womply" ///
					6 "Online Sales, Second Measure" ///
					7 "Brick & Mortar Sales, Second Measure" ///
					8 "Overall Sales, Second Measure")) ///
		legend(size(small) rows(4) colgap(0.4cm) region(lstyle(none))) ///
		ytitle("% change" , color(black) size(small) axis(1)) ///
	    ytitle("" , color(black) size(small) axis(2)) ///
		xtitle("") ///
		xlabel(#4 , labsize(small) ) ///
	    ylabel(-50(25)100 ,angle(horizontal) nogrid axis(1) ) ///
		ylabel( -50(25)100 ,angle(horizontal) nogrid axis(2) ) ///
		note("Note: Vertical line marks the date of the first stay-at-home order (March 17th, 2020)." , size(small) ///
		color(black))
			 
graph export figures/aggregate/aggregate.pdf , as(pdf) replace 


  
*===============================================================================
* Second Measure Data - Get Graphs 
*===============================================================================

* load  data 
  use "data/clean/second_measure/sector_channel_daily_sales.dta" , clear 
  
* collapse to average daily 
  collapse sales  , by(date sector) 
  
* reshape to calculate % change 
  encode sector , gen(sec)
  lab list sec
  drop sector 
  rename sales sales_
  reshape wide sales_ , i(date) j(sec) 
   
* calculate % change from a year ago , same weekday  
  g dow = dow(date)
  g month = month(date) 
  g year = yofd(date) 
  tab month if year == 2020
  drop if month>3 & year == 2019 
  
 qui foreach var of varlist sales_* { 
	  g gr_`var' = . 
	  forv day = 0/6{ 
		summ  `var' if year == 2019 & dow == `day'
		local base = `r(mean)' 
		replace gr_`var' = (`var'/`base')*100 - 100 if dow == `day' 
		} 
} 
	
* trim data 
  keep if year == 2020
  keep date gr_sales_* 
  
* trim to get a national 14-day lag 
  drop if date>td(17apr2020) 
  
* label variables for Ezra 
 keep date gr_sales_2 gr_sales_1 gr_sales_6 gr_sales_7 gr_sales_8 gr_sales_9  ///
		 gr_sales_15 gr_sales_18 gr_sales_19 gr_sales_20
		 
tsset date 
 foreach var of varlist gr_sales_2 gr_sales_1 gr_sales_6 gr_sales_7 ///
		gr_sales_8 gr_sales_9  ///
		 gr_sales_15 gr_sales_18 gr_sales_19 gr_sales_20 { 
		 
		 tssmooth ma `var'_ma = `var', window(7 1) 
		 drop `var' 
		 ren `var'_ma `var'
		} 
   
  lab var gr_sales_2 "Amazon" 
  lab var gr_sales_1 "Accomodation & Food Services" 
  lab var gr_sales_6 "Finances & Insurance" 
  lab var gr_sales_7 "Food Delivery" 
  lab var gr_sales_8 "General/Wholesale Retail" 
  lab var gr_sales_9 "Grocery Stores" 
  lab var gr_sales_15 "Pharmacies" 
  lab var gr_sales_18 "Restaurants" 
  lab var gr_sales_19 "Retail Trade" 
  lab var gr_sales_20 "Transportation" 

* graph for amazon and 8 sectors  
  format date %tdMonyyyy
  tsset date 
  graph twoway (tsline gr_sales_2 gr_sales_1 gr_sales_6 gr_sales_7 gr_sales_8 gr_sales_9  ///
		 gr_sales_15 gr_sales_18 gr_sales_19 gr_sales_20  , ///
	     lcolor(blue purple red lavender midgreen forest_green orange midblue brown )) ///
		(tsline gr_sales_2 gr_sales_1 gr_sales_6 gr_sales_7 gr_sales_8 gr_sales_9  ///
		 gr_sales_15 gr_sales_18 gr_sales_19 gr_sales_20   , ///
	     lcolor(none none none none none none none none none none none ) yaxis(2)) ,  ///
	     graphregion(color(white)) ///
	     legend(order (1 "Amazon" ///
				    2 "Accomodation & Food Services" ///
					3 "Finances & Insurance" ///
					4 "Food Delivery" ///
					5 "General/Wholesale Retail" ///
					6 "Grocery Stores" ///
					7 "Pharmacies" ///
					8 "Restaurants" ///
					9 "Retail Trade" ///
					10 "Transportation" )) ///
		legend(size(small) rows(5) region(lstyle(none))) ///
		ytitle("% change" , color(black) size(small)) ///
		title("Consumer Spending by Sector, from Second Measure" , color(black) size(medium)) ///
		xtitle("") ///
		xlabel(#4 , labsize(small) ) ///
		ylabel(,angle(horizontal) nogrid) ///
		ylabel(,angle(horizontal) nogrid axis(2)) ///
		tline(17mar2020, lcolor(black) ) ///
		note("Note: Vertical line marks the date of the first stay-at-home order (March 17th, 2020)." , size(small) ///
		color(black)) 

		
graph export figures/aggregate/amazon_others.pdf , as(pdf) replace 

save "figures/aggregate/amazon_others.dta" , replace 



*===============================================================================
* Scatter Plot of Effective Date vs. First Death 
*===============================================================================

* get first death data
u "data/clean/nyt_cases/nyt_cases_04_17.dta" , clear
sort county_fips date  
tab date 

* identify date of first case 
  bys county_fips : egen first_case_date = min(date) 
  format first_case_date %td 
  
* identify date of first death 
  g tag = 1 if deaths>0 
  bys county_fips : egen first_death_date = min(date) if tag == 1 
  format first_death_date %td 
  gsort county_fips -first_death_date
  by county_fips : carryforward first_death_date , replace 
  bys county_fips : keep if _n == 1 
  keep county_fips first_death_date first_case_date 

* merge in effective date data
preserve 
 use  "data/clean/stay_at_home/stay_at_home_AM.dta" , clear 
 egen test = nvals(effective_date) , by(county_fips) 
 tab test 
 drop test 
 bys county_fips : keep if _n == 1
 keep county_fips effective_date 
 tempfile stay_at_home
 save `stay_at_home' , replace 
restore 

merge 1:1 county_fips using `stay_at_home' 
tab _merge 
keep if _merge == 3
drop _merge 

foreach var of varlist first_case_date first_death_date effective_date { 
	   format `var' %tdddMon
} 
 
* effective date vs. first death 
	scatter effective_date first_death_date   , ///
	ytitle("Date of Stay at Home Order" , color(black) size(medium)) ///
	xtitle("Date of First Death" , color(black) size(medium)) ///
	ylabel( , angle(horizontal)) ///
	graphregion(color(white)) ///
	title("Date of Effective Stay at Home Order vs. First Death" , color(black) size(medium)) 
	
	graph export "figures/aggregate/scatter_order_death.pdf" , as(pdf) replace 
	
* effective date vs. first case
	scatter effective_date first_case_date   , ///
	ytitle("Date of Stay at Home Order" , color(black) size(medium)) ///
	xtitle("Date of First Case" , color(black) size(medium)) ///
	ylabel( , angle(horizontal)) ///
	graphregion(color(white)) ///
	title("Date of Effective Stay at Home Order vs. First Case" , color(black) size(medium)) 
	
    graph export "figures/aggregate/scatter_order_case.pdf" , as(pdf) replace 

